const Service = require("egg").Service

class RecommendService extends Service {

  baseSql = ` , user.coverPictureUrl as userPic,
  user.musicCount,user.musicPlayCount,user.nickname,user.type 
  FROM table INNER JOIN user ON `

  // 需要联表查询这首歌对应的用户信息
  async getList(page,pageSize){

    let model = ['user','song','article','video'];

    // 返回的结构{count:xxx,rows:[]}
    // 获取从page行开始的 pageSize的数据
    let $users = await this.getUsersModel(page,pageSize);
    let $songs = await this.getSongsModel(page,pageSize);
    let $articles = await this.getArticlesModel(page,pageSize);
    let $videos = await this.getVideosModel(page,pageSize);


    // 随机生成10条数据
    let randModels = this.getRandModels(pageSize,model);

    let $result = [];

    /**
     * 返回的结构
     * [{user:{}},{song:{}},{user:{}}]
     * 
     */

    randModels.forEach((val,index)=>{
      switch (val) {
        case model[0]:
          $result[index] = {[val + 'Entity']:$users.rows[index] || null}
          break
        case model[1]:
          $result[index] = {[val + 'Entity']:$songs.rows[index] || null}
          break
        case model[2]:
          $result[index] = {[val + 'Entity']:$articles.rows[index] || null}
          break
        case model[3]:
          $result[index] = {[val + 'Entity']:$videos.rows[index] || null}
          break
      }
    })




    let start = parseInt((page-1)*pageSize)
    let users = await this.getUsers(start,pageSize)
    let songs = await this.getSongs(start,pageSize)
    let articles = await this.getArticles(start,pageSize)
    let videos = await this.getVideos(start,pageSize)
    // 随机生成pageSize条数据
    let data = [...users,...songs,...articles,...videos],res = []
    // 随机生成0-data.length-1的下标
    // Math.floor(Math.random() * data.length)
    for(let i = 0; i < pageSize; i++){  
      res[i] = data[Math.floor(Math.random() * data.length)]
    }
    return $result
  }

  getRandModels(pageSize,model){
    //['user','user','song']
    let randModels = []
    // 0 到 3 的随机数的下标
    //  伪随机数在范围从0 到小于1，也就是说，从 0（包括 0）往上，但是不包括 1（排除 1）
    for(let i = 0; i < pageSize; i++){
        let randKey = Math.floor(Math.random() * model.length)
        randModels[i] = model[randKey]
    }
    return randModels
  }

  // 使用sequelize插件操作数据库 
  // 查询歌手的操作
  // 获取用户的数据偏移每次是page 即第一页：0 - 9 第二页 ：1 - 10
  async getUsersModel(page,pageSize){
    let userModel = this.ctx.model.User
    let result = await userModel.findAndCountAll(
      { 
        offset: page, 
        limit: pageSize ,
        where:{
          type :"DQ_SINGER",
         }
      }
    )
    return result
  }

  // 获取歌曲模型
  async getSongsModel(page,pageSize){
    // 关联用户表
    const userModel = this.ctx.model.User
    const temp = this.ctx.model.Song
    userModel.hasMany(temp);
    temp.belongsTo(userModel);
    const result = await temp.findAndCountAll({ 
        include: userModel,
        offset: page, 
        limit: pageSize ,
      }
    )
    return result
  }

  // 获取文章模型
  async getArticlesModel(page,pageSize){
    // 关联用户表
    const userModel = this.ctx.model.User
    const temp = this.ctx.model.Article
    userModel.hasMany(temp);
    temp.belongsTo(userModel);
    const result = await temp.findAndCountAll({ 
        include: userModel,
        offset: page, 
        limit: pageSize ,
      }
    )


    result.rows.forEach((item) => {
      item.coverUrlList = item.coverUrlList.split(",")
    });

    return result
  }


  // 获取视频模型
  async getVideosModel(page,pageSize){
    // 关联用户表
    const userModel = this.ctx.model.User
    const temp = this.ctx.model.Video
    userModel.hasMany(temp);
    temp.belongsTo(userModel);
    const result = await temp.findAndCountAll({ 
        include: userModel,
        offset: page, 
        limit: pageSize ,
      }
    )
    return result
  }




  // 只取歌手的一行的数据
  async getUsers(start,pageSize){
    let sql = `SELECT * FROM user WHERE type = 'DQ_SINGER' limit ? , ?`
    return await this.app.mysql.query(sql,[start,pageSize])
  }

  // 只取歌曲的一行的数据
  async getSongs(start,pageSize){
    let sql = `SELECT song.* ${this.baseSql.replace("table","song")} song.userId = user.id limit ? , ?`
    return await this.app.mysql.query(sql,[start,pageSize])
  }

  // 只取文章的一行的数据
  async getArticles(start,pageSize){
    let sql = `SELECT article.* ${this.baseSql.replace("table","article")} article.userId = user.id limit ? , ?`
    return await this.app.mysql.query(sql,[start,pageSize])
  }

  // 只取歌手的一行的数据
  async getVideos(start,pageSize){
    let sql = `SELECT video.* ${this.baseSql.replace("table","video")} video.userId = user.id limit ? , ?`
    return await this.app.mysql.query(sql,[start,pageSize])
  }

}

module.exports = RecommendService
